#! -*-coding: utf-8-*-
"""
数据库工具
"""
import sqlite3, time
from user import *


class DbError(Exception):
    pass


class DbUtil:
    def __init__(self, db_name="user.db"):
        self.__db_name = db_name
        self.__conn = sqlite3.connect(self.__db_name)
        try:
            create_tbl_sql = '''create table if not exists user(
             USER_ID      INTEGER       PRIMARY KEY,
             USER_NAME    TEXT          NOT NULL UNIQUE,
             USER_PWD     TEXT          NOT NULL,
             USER_ONLINE_TIME INTEGER   NOT NULL);'''
            self.__conn.execute(create_tbl_sql)
        except:
            raise DbError("Create Table Error!")
        self.__cursor = self.__conn.cursor()

    def __del__(self):
        self.__conn.close()

    def display_user_message(self):
        """
        desc:   显示数据库中所有用户的信息:ID, Name, Pwd, 在线时长
        """
        sql = "select USER_ID, USER_NAME, USER_PWD, USER_ONLINE_TIME from user"
        msg = self.__cursor.execute(sql).fetchall()
        for row_msg in msg:
            out_msg = "ID:{0}, Name:{1}, Pwd:{2}, Online-time:{3}秒".format(
                row_msg[0], row_msg[1], row_msg[2], row_msg[3])
            print(out_msg.decode("utf-8"))

    def delete_table(self):
        """
        desc:   删除数据表
        """
        sql = "drop table '%s'" % ("user")
        self.__cursor.execute(sql)
        self.__conn.commit()

    def has_user_name(self, user_name):
        """
        desc:   查询用户名是否存在
        param:
            user_name:  待查询的用户名
        return:
            bool: 用户名是否存在
        """
        sql = '''
              select USER_ID from user where USER_NAME = '%s'
              ''' % user_name
        res = self.__cursor.execute(sql).fetchall()
        if len(res) == 0:
            return False
        else:
            return True

    def register_user(self, user_name, user_pwd):
        """
        desc:   注册用户, 插入用户名和密码
        param:
            user_name:  注册的用户名
            user_pwd:   注册的用户名对应的密码
        return: 空
        """
        sql = "insert into user values(NULL, '%s', '%s', '%d')" \
            % (user_name, user_pwd, 0)
        self.__cursor.execute(sql)
        self.__conn.commit()

    def can_login(self, user_name, user_pwd):
        """
        desc:   查询用户名是否可以登录
        param:
            user_name:  查询的用户名
            user_pwd:   查询的用户名对应的密码
        return:
            bool:   是否可以登录
        """
        sql = "select * from user where USER_NAME = '%s' and USER_PWD = '%s'" \
            % (user_name, user_pwd)
        res = self.__cursor.execute(sql).fetchall()
        if len(res) > 0:
            return True
        else:
            return False

    def get_user_online_time(self, user_name):
        """
        desc:   获取用户的在线时间
        param:
            user_name:  查询的用户名
        return:
            int:   在线时间
        """
        sql = "select USER_ONLINE_TIME from user where USER_NAME = '%s'" \
            % (user_name,)
        res = self.__cursor.execute(sql).fetchall()
        if len(res) > 0:
            return res[0][0]
        else:
            return None

    def update_user_online_time(self, user_name, user_online_time):
        """
        desc:   更新用户的在线时间
        param:
            user_name:  更新的用户名
            user_online_time:   更新的在线时间
        return: 空
        """
        sql = "update user set USER_ONLINE_TIME = '%d' where USER_NAME = '%s'" \
            % (user_online_time, user_name)
        self.__cursor.execute(sql)
        self.__conn.commit()

if __name__ == "__main__":
    db = DbUtil()
    db.display_user_message()